package com.github.thinwonton.excel2sql.sevice;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.map.MapUtil;
import com.github.thinwonton.excel2sql.conf.GenerateProperties;
import com.github.thinwonton.excel2sql.model.ExcelRow;
import com.github.thinwonton.excel2sql.model.SqlTable;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;
import java.util.function.Consumer;

public class ReadExcel {

    private final Logger LOGGER = LoggerFactory.getLogger(ReadExcel.class);

    private GenerateProperties properties;

    private List<SqlTable> tables;

    public ReadExcel(GenerateProperties properties) {
        this.properties = properties;
        this.tables = new ArrayList<>();
        readExcel();
    }

    private void readExcel() {
        //调整为excel以0开始的index
        int beginIndex = this.properties.getBeginIndex() - 1;
        int endIndex = this.properties.getEndIndex();

        //获取正确的endIndex
        try {
            if (endIndex < 0) {
                int sheetCount = readSheetCount();
                endIndex = sheetCount - 1;
            } else {
                endIndex = endIndex - 1;
            }
        } catch (IOException e) {
            throw new RuntimeException("读取excel发生错误", e);
        }

        while (endIndex >= beginIndex) {
            SqlTable table = new SqlTable();
            if (readTableInfo(table, beginIndex) == null) {
                LOGGER.warn("读取不到表的定义, index=" + beginIndex);
            } else {
                List<ExcelRow> excelRows = readFiled(beginIndex);
                if (CollectionUtil.isEmpty(excelRows)) {
                    LOGGER.warn("读取不到表字段, index=" + beginIndex);
                } else {
                    table.setRows(excelRows);
                    tables.add(table);
                }
            }

            beginIndex++;
        }
    }

    private int readSheetCount() throws IOException {
        String path = properties.getExcelPath();
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        return workbook.getNumberOfSheets();
    }


    /**
     * 输出到文件
     */
    public void outputFile() {
        if (StringUtils.isBlank(properties.getExportSqlDir())) {
            throw new RuntimeException("输出目录为空");
        }

        if (CollectionUtil.isEmpty(tables)) {
            LOGGER.info("table infos are empty.");
            return;
        }

        Map<SqlTable, String> mapping = new LinkedHashMap<>();
        for (SqlTable table : tables) {
            String script = toScriptStr(table);
            if (StringUtils.isEmpty(script)) {
                LOGGER.info("ignore " + table.toString());
            } else {
                mapping.put(table, script);
            }
        }

        if (properties.isScriptPerFile()) {
            //每个表一个SQL文件
            mapping.forEach(new BiConsumer<SqlTable, String>() {
                @Override
                public void accept(SqlTable sqlTable, String script) {
                    StringBuffer sb = new StringBuffer();
                    sb.append("-- Table structure for ").append(sqlTable.getTableName()).append("\n");
                    sb.append(script);
                    String fileName = sqlTable.getTableName() + "." + "sql";
                    FileUtil.writeString(sb.toString(), new File(properties.getExportSqlDir(), fileName), "UTF-8");

                    LOGGER.info("成功生成脚本:" + fileName);
                }
            });

        } else {
            //所有的SQL在同一个文件
            StringBuffer sb = new StringBuffer();
            mapping.forEach(new BiConsumer<SqlTable, String>() {
                @Override
                public void accept(SqlTable sqlTable, String script) {
                    sb.append("-- Table structure for ").append(sqlTable.getTableName()).append("\n");
                    sb.append(script).append("\n\n");
                }
            });
            String fileName = "mysql_script.sql";
            FileUtil.writeString(sb.toString(), new File(properties.getExportSqlDir(), fileName), "UTF-8");
            LOGGER.info("成功生成脚本:" + fileName);
        }

    }

    private String toScriptStr(SqlTable table) {
        StringBuilder sb = new StringBuilder();

        //drop
        if (properties.isDropSQL()) {
            sb.append("DROP TABLE IF EXISTS `").append(table.getTableName()).append("`")
                    .append(";")
                    .append("\n");
        }

        sb.append("CREATE TABLE `").append(table.getTableName()).append("` (")
                .append("\n");

        String primaryKey = null;
        //索引
        List<IndexPair> indexPairs = new ArrayList<>();

        for (ExcelRow row : table.getRows()) {
            sb.append("`").append(row.getFieldName()).append("` ").append(row.getDataType()).append(" ");
            //Not null
            if (StringUtils.equals(StringUtils.lowerCase(row.getNotNull()), "yes")) {
                sb.append(" NOT NULL ");
            }

            //获取主键
            if (StringUtils.equals(StringUtils.lowerCase(row.getPrimaryKey()), "yes")) {
                primaryKey = row.getFieldName();
            }

            //是否有默认值
            if (StringUtils.isNotBlank(row.getDefaultVal())) {
                //是否是主键
                if (StringUtils.equals(primaryKey, row.getFieldName())) {
                    sb.append(" ").append(row.getDefaultVal()).append(" ");
                } else {
                    sb.append(" DEFAULT ").append(row.getDefaultVal());
                }
            }

            if (StringUtils.isNotBlank(row.getIndexType())) {
                if (StringUtils.equalsIgnoreCase(IndexType.NORMAL.name(), row.getIndexType())) {
                    indexPairs.add(new IndexPair(row.getFieldName(), IndexType.NORMAL));
                } else if (StringUtils.equalsIgnoreCase(IndexType.UNIQUE.name(), row.getIndexType())) {
                    indexPairs.add(new IndexPair(row.getFieldName(), IndexType.UNIQUE));
                }
            }

            //注释
            sb.append(" COMMENT '").append(row.getFieldComment()).append("',").append("\n");
        }

        sb.append("PRIMARY KEY (`").append(primaryKey).append("`)").append("\n");

        //添加索引
        if (CollectionUtil.isNotEmpty(indexPairs)) {
            sb.replace(sb.length() - 1, sb.length(), ",\n"); //主键那里添加逗号

            //循环添加索引
            indexPairs.forEach(new Consumer<IndexPair>() {
                @Override
                public void accept(IndexPair indexPair) {
                    if (indexPair.getType().equals(IndexType.NORMAL)) {
                        sb.append("INDEX ").append("IDX_").append(indexPair.getField().toUpperCase()).append(" (`").append(indexPair.getField()).append("`)");
                    } else if (indexPair.getType().equals(IndexType.UNIQUE)) {
                        sb.append("UNIQUE INDEX ").append("IDX_").append(indexPair.getField().toUpperCase()).append(" (`").append(indexPair.getField()).append("`)");
                    }

                    //逗号
                    sb.append(",").append("\n");
                }
            });

            //删除最后的逗号
            sb.replace(sb.length() - 2, sb.length(), "\n");
        }

        //闭包
        sb.append(")");

        sb.append(" ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COMMENT='").append(table.getTableDesc())
                .append("';");
        return sb.toString();
    }

    public static void main(String[] args) {
        StringBuffer sb = new StringBuffer();
        sb.append("aa").append("\n");
        sb.replace(sb.length() - 1, sb.length(), ",2");
        System.out.println(sb.toString());
    }

    /**
     * 获取表
     */
    private SqlTable readTableInfo(SqlTable table, int index) {
        ImportParams params = new ImportParams();
        params.setKeyMark(":"); //获取:后缀的
        params.setReadSingleCell(true);
        params.setTitleRows(2);
        params.setStartSheetIndex(index);
        params.setLastOfInvalidRow(3);
        ExcelImportResult<Map<String, Object>> result = ExcelImportUtil.importExcelMore(
                new File(properties.getExcelPath()),
                Map.class, params);

        Map<String, Object> map = result.getMap();
        if (MapUtil.isEmpty(map)) {
            return null;
        }

        String tableName = MapUtil.getStr(map, "Table Name:");
        String tableDesc = MapUtil.getStr(map, "Table Comment:");
        if (StringUtils.isBlank(tableName)) {
            return null;
        }
        table.setTableName(tableName.trim());
        table.setTableDesc(tableDesc.trim());
        return table;
    }


    /**
     * 获取字段
     *
     * @param sheetIndex
     * @return
     */
    private List<ExcelRow> readFiled(int sheetIndex) {
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setTitleRows(11);
        params.setStartSheetIndex(sheetIndex);

        params.setVerifyHandler(obj -> {
            ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
            ExcelRow row = (ExcelRow) obj;
            if (StringUtils.isNotBlank(row.getFieldName()) && StringUtils.isNotBlank(row.getFieldComment())
                    && StringUtils.isNotBlank(row.getDataType())) {
                result.setSuccess(true);
                //去除空格
                row.setFieldComment(StringUtils.trim(row.getFieldComment()));
                row.setFieldName(StringUtils.trim(row.getFieldName()));
                row.setDataType(StringUtils.trim(row.getDataType()));
                row.setPrimaryKey(StringUtils.trim(row.getPrimaryKey()));
                row.setNotNull(StringUtils.trim(row.getNotNull()));
                row.setDefaultVal(StringUtils.trim(row.getDefaultVal()));
            } else {
                result.setSuccess(false);
                result.setMsg("数据为空");
            }
            return result;
        });

        List<ExcelRow> list = ExcelImportUtil.importExcel(
                new File(properties.getExcelPath()),
                ExcelRow.class, params);
        return list;
    }

    public static class IndexPair {
        private String field;
        private IndexType type;

        public IndexPair(String field, IndexType type) {
            this.field = field;
            this.type = type;
        }

        public String getField() {
            return field;
        }

        public IndexType getType() {
            return type;
        }
    }

    public enum IndexType {
        NORMAL,
        UNIQUE
    }
}
